import os
import time
from contextlib import contextmanager
from sqlalchemy import create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import QueuePool
from app.core.config import settings
from app.core.logging import get_logger

logger = get_logger(__name__)

# 设置环境变量以避免编码问题
os.environ['PGCLIENTENCODING'] = 'utf-8'
os.environ['PYTHONIOENCODING'] = 'utf-8'

# 解析并清理数据库URL
import urllib.parse
database_url = str(settings.DATABASE_URL)
parsed_url = urllib.parse.urlparse(database_url)

# 重新构建URL，确保各部分正确编码
username = urllib.parse.quote_plus(parsed_url.username) if parsed_url.username else ""
password = urllib.parse.quote_plus(parsed_url.password) if parsed_url.password else ""

if username and password:
    netloc = f"{username}:{password}@{parsed_url.hostname}"
elif username:
    netloc = f"{username}@{parsed_url.hostname}"
else:
    netloc = parsed_url.hostname
    
if parsed_url.port:
    netloc = f"{netloc}:{parsed_url.port}"

# 重新构建URL
safe_url = urllib.parse.urlunparse((
    parsed_url.scheme,
    netloc,
    parsed_url.path,
    parsed_url.params,
    parsed_url.query or "client_encoding=utf8",
    parsed_url.fragment
))

# 高性能数据库连接池配置
engine = create_engine(
    safe_url,
    # 连接池配置 - 优化性能
    poolclass=QueuePool,
    pool_size=getattr(settings, 'DATABASE_POOL_SIZE', None) or min(10, max(5, os.cpu_count() or 4)),  # 动态调整连接池大小
    max_overflow=getattr(settings, 'DATABASE_MAX_OVERFLOW', None) or 20,  # 最大溢出连接数
    pool_timeout=getattr(settings, 'DATABASE_POOL_TIMEOUT', None) or 30,  # 获取连接的超时时间（秒）
    pool_recycle=getattr(settings, 'DATABASE_POOL_RECYCLE', None) or 1800,  # 连接回收时间（秒），避免数据库连接超时
    pool_pre_ping=True,  # 在获取连接时预检查连接是否有效
    
    # 连接参数
    connect_args={
        "client_encoding": "utf8",
        "options": "-c client_encoding=utf8 -c timezone=UTC -c statement_timeout=30000",
        "connect_timeout": getattr(settings, 'DATABASE_CONNECT_TIMEOUT', None) or 10,  # 连接超时时间（秒）
        "application_name": "fastapi-platform",  # 应用名称，便于数据库监控
        "keepalives": 1,  # 启用TCP保持连接
        "keepalives_idle": 30,  # TCP保持连接空闲时间
        "keepalives_interval": 10,  # TCP保持连接间隔
        "keepalives_count": 5,  # TCP保持连接次数
    },
    
    # 性能优化
    echo_pool=settings.DEBUG,  # 调试模式下显示连接池信息
    echo=settings.DEBUG,  # 调试模式下打印SQL语句
    future=True,  # 启用SQLAlchemy 2.0风格
    
    # 查询优化
    execution_options={
        "isolation_level": "READ_COMMITTED",  # 事务隔离级别
        "autocommit": False,  # 禁用自动提交
        "compiled_cache_size": 500,  # 查询编译缓存大小
        "stream_results": False,  # 禁用流式结果以提高性能
    },
    
    # 缓存和性能
    max_identifier_length=63,  # PostgreSQL标识符最大长度
    hide_parameters=False,  # 显示参数（调试时有用）
)

# 添加数据库连接事件监听器
@event.listens_for(engine, "connect")
def set_connection_options(dbapi_connection, connection_record):
    """设置数据库连接选项"""
    try:
        # 设置连接级别参数
        cursor = dbapi_connection.cursor()
        cursor.execute("SET statement_timeout = 30000;")  # 30秒超时
        cursor.execute("SET idle_in_transaction_session_timeout = 60000;")  # 60秒空闲超时
        cursor.execute("SET lock_timeout = 30000;")  # 30秒锁超时
        cursor.close()
        logger.debug("Database connection options configured")
    except Exception as e:
        logger.warning(f"Failed to set connection options: {e}")

@event.listens_for(engine, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    """检查连接是否有效"""
    try:
        cursor = dbapi_connection.cursor()
        cursor.execute("SELECT 1")
        cursor.close()
    except Exception as e:
        logger.warning(f"Database connection is invalid, reconnecting: {e}")
        raise Exception("Database connection is invalid")
# 增强的会话工厂配置
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
    expire_on_commit=False,  # 提交后不使对象过期
    info={},  # 会话信息字典
)

Base = declarative_base()

# 连接池监控
class ConnectionPoolMonitor:
    """数据库连接池监控器"""
    def __init__(self, engine):
        self.engine = engine
        self.last_check = time.time()
        
    def get_pool_status(self):
        """获取连接池状态"""
        pool = self.engine.pool
        return {
            "checked_out": pool.checkedout(),
            "checked_in": pool.checkedin(),
            "size": pool.size(),
            "overflow": pool.overflow(),
            "checked_out_connections": len(pool._checkedout),
        }

# 创建监控器实例
pool_monitor = ConnectionPoolMonitor(engine)

# 增强的依赖注入
def get_db():
    """获取数据库会话，支持自动重试和连接池监控"""
    max_retries = 3
    retry_delay = 1.0
    
    for attempt in range(max_retries):
        try:
            db = SessionLocal()
            
            # 测试连接是否有效
            db.execute("SELECT 1")
            
            try:
                yield db
            finally:
                db.close()
            
            # 成功则退出循环
            break
            
        except Exception as e:
            logger.warning(f"Database connection attempt {attempt + 1} failed: {e}")
            
            if attempt < max_retries - 1:
                time.sleep(retry_delay)
                retry_delay *= 2  # 指数退避
            else:
                logger.error("All database connection attempts failed")
                raise

# 异步数据库支持（兼容层）
class AsyncDatabaseManager:
    """异步数据库管理器（为未来异步支持做准备）"""
    def __init__(self, engine):
        self.engine = engine
        
    async def get_session(self):
        """获取异步数据库会话（占位符）"""
        # 当前使用同步会话，但保留接口为未来异步支持做准备
        db = SessionLocal()
        try:
            yield db
        finally:
            db.close()

# 创建异步管理器实例
async_db_manager = AsyncDatabaseManager(engine)

# 数据库健康检查
def check_database_health():
    """检查数据库连接健康状态"""
    try:
        with engine.connect() as conn:
            result = conn.execute("SELECT 1 as health_check")
            health_result = result.scalar()
            
        pool_status = pool_monitor.get_pool_status()
        
        return {
            "status": "healthy" if health_result == 1 else "unhealthy",
            "pool_status": pool_status,
            "timestamp": time.time(),
        }
    except Exception as e:
        logger.error(f"Database health check failed: {e}")
        return {
            "status": "unhealthy",
            "error": str(e),
            "timestamp": time.time(),
        }